import java.sql.*;
import java.io.*;

/** ExecuteSQL.java
 *    To execuate a SQL statement, the following 5 steps must be following:
 *	1. Load necessary JDBC Driver by either
 *		Class.forName(driverName); or
 *		DriverManager.registerDriver(driverClassObject);
 *	2. Make connection to database server by
 *		Connection cnn = DriverManager.getConnection(url, user, password);
 *	3. Create a statement by
 *		Statement stmt = cnn.createStatement();
 *	4. Execute a SQL statement by one of the following execute statements.
 *		ResultSet res = executeQuery( selectSQl );
 *		int       k   = executeUpdate( updateSQL );
 *		boolean   b   = execute( selectOrUpdateSQL );
 *		int []	  k   = executeBatch();
 *	5. Display result set(s) by select statement or the number of records effected
 *	   update SQL statement.
 *
 *     This program asks for a SQL statement, either select or update statement,
 *  and execute that statement. The Statement class's execute():
 *		boolean execute(String sql)
 *  is used to execute a SQL statement. The returned value indicate whether
 *  the sql statement generate a result set or not.
 *      If the returned  value is true, then the sql statement returns one
 *  or more resultsets. Use 
 *		boolean		getMoreResults(); to move to the next result set.
 *		ResultSet	getResultSet();   to get current result set.
 *      If the execute() return false, then the sql statement is a DML statement.
 *  Use int getUpdateCount() to know the number of records effected by the statment.
 */

public class ExecuteSQL {

   Connection	cnn = null;
   Statement	stmt = null;
   ResultSet	res = null;

   public ExecuteSQL () {
	String	url = "jdbc:oracle:thin:@prover.cs.csubak.edu:1521:PROVERDB",
			user = "CS440",
			passwd = "student1";
	try {	
		// 1. Load driver.
		Class.forName("oracle.jdbc.driver.OracleDriver"); 
	
		// 2. Make a connection.
		cnn = DriverManager.getConnection(url, user, passwd);

		// 3. Create a statement object.
		stmt = cnn.createStatement();
     	} catch ( Exception e ) { e.printStackTrace(); }
   }

   public static void main(String args[]) {

        String	sql;
 	ExecuteSQL ex = new ExecuteSQL();
        boolean	hasResultSet;

	while ( true ) {
	    try {	

		sql = ScreenIO.promptForString("Enter a SQL statement (or exit): ");

		if ( sql.trim().length() == 0 ) continue;
		if ( sql.charAt(sql.length()-1) == ';' ) sql = sql.substring(0, sql.length() - 1); // get rid of ;
		if ( sql.equals("exit") ) break;

			// 4. Execute SQL statement.
			hasResultSet = 	ex.stmt.execute(sql);

			// 5. Test whether there result set(s) or not. If not, print updateCount
			//    Otherwise, prints out result set.

			if ( hasResultSet ) {
			    do {
				ex.res = ex.stmt.getResultSet();
				ScreenIO.displayResultSet(ex.res);
			    } while ( ex.stmt.getMoreResults() ) ;
			} else {
				System.out.println("Records effected: " + ex.stmt.getUpdateCount());
			}
	     } catch (Exception e) { System.out.println(e.toString()); }
	}
	try { ex.res.close(); ex.stmt.close(); ex.cnn.close(); }
	catch ( SQLException e3 ) { e3.printStackTrace(); }
   }

}
